﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3c.org/TR/1999/REC-html401-19991224/loose.dtd">
<!-- saved from url=(0038)http://lists.mysql.com/internals/34377 -->
<HTML xmlns="http://www.w3.org/1999/xhtml"><HEAD><TITLE>MySQL Lists: internals: Re: RBAC Support</TITLE>
<META http-equiv=Content-Type content="text/html; charset=utf-8"><LINK 
href="http://www.mysql.com/common/favicon.ico" rel="shortcut icon"><LINK 
media=screen href="Internal concerpts of MySQL about RBAC.files/screen.css" 
type=text/css rel=stylesheet>
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY>
<DIV id=header>
<DIV id=logo><A href="http://www.mysql.com/"><IMG height=52 alt=MySQL 
src="Internal concerpts of MySQL about RBAC.files/mysql_100x52-64.gif" width=100 
border=0></A></DIV>
<DIV id=searchandlogin>
<DIV id=searchbox>
<FORM style="FLOAT: none" action=http://www.mysql.com/search/ method=get><INPUT 
class=frm maxLength=255 name=q> <INPUT class=submit2 type=submit value=Search> 
</FORM></DIV>
<DIV id=login>
<P class=s><A 
href="http://www.mysql.com/login.php?dest=http%3A%2F%2Flists.mysql.com%2Finternals%2F34377">Login</A> 
/ <A href="http://www.mysql.com/register.php">Register</A></P></DIV></DIV></DIV>
<DIV id=nav>
<UL>
  <LI><A href="http://dev.mysql.com/">Developer Zone</A> </LI>
  <LI><A href="http://lists.mysql.com/">Lists Home</A> </LI>
  <LI><A href="http://lists.mysql.com/faq.php">FAQ</A> </LI>
  <LI><A href="http://lists.mysql.com/search.php">Advanced search</A> 
</LI></UL></DIV><BR clear=both>
<DIV id=content>
<TABLE cellSpacing=2 cellPadding=2 width="100%" border=0>
  <TBODY>
  <TR>
    <TD class=headerlabel>List:</TD>
    <TD class=headervalue><A 
      href="http://lists.mysql.com/internals">Internals</A></TD>
    <TD class=headervalue colSpan=2><A 
      href="http://lists.mysql.com/internals/34376">« Previous Message</A><A 
      style="FLOAT: right" href="http://lists.mysql.com/internals/34378">Next 
      Message »</A></TD></TR>
  <TR>
    <TD class=headerlabel>From:</TD>
    <TD class=headervalue>Sergei&nbsp;Golubchik</TD>
    <TD class=headerlabel>Date:</TD>
    <TD class=headervalue>March&nbsp;7&nbsp;2007&nbsp;11:40pm</TD></TR>
  <TR>
    <TD class=headerlabel>Subject:</TD>
    <TD class=headervalue colSpan=3>Re: RBAC Support<BR></TD></TR>
  <TR>
    <TD class=headervalue align=right colSpan=4><A 
      href="http://lists.mysql.com/internals/34377?f=plain">Get Plain Text</A> 
    &nbsp;</TD></TR></TBODY></TABLE><PRE>Hi!

First - a summary.
We, in MySQL, are usually more interested in implementing features that
are part of the SQL standard, than non-standard extensions.

SQL standard specifies what a "role" is, how it behaves, what properties it has,
and what statements exist to manipulate roles.

That is, although the functionality is mostly the same as you've
implemented, the syntax is close but different.

See below.

On Mar 02, Ian Molloy wrote:
<SPAN class=quote1>&gt; We implemented core RBAC (RBAC-0) which includes just user-role
</SPAN><SPAN class=quote1>&gt; assignment (UA), and role-permission assignment (PA). All permissions
</SPAN><SPAN class=quote1>&gt; were stored in tables in the mysql database, in a very similar manner
</SPAN><SPAN class=quote1>&gt; to what is currently done. For those familiar with the RBAC
</SPAN><SPAN class=quote1>&gt; standards, we did not implement any type of sessions (which is in
</SPAN><SPAN class=quote1>&gt; RBAC-0 but rarely implemented), role hierarchies (RBAC-1, which I'd
</SPAN><SPAN class=quote1>&gt; like to add), or constrains (RBAC-2, which would be a far off goal).
</SPAN>
This is ok.

<SPAN class=quote1>&gt; The mysql client was augmented to include a flag followed by a list
</SPAN><SPAN class=quote1>&gt; of roles the user wishes to activate when they log in.
</SPAN><SPAN class=quote1>&gt; 	$ mysql [-u &lt;username&gt;] [-l &lt;role1,role2,...rolen&gt;]
</SPAN>
Please no, these's SET ROLE for this and default role. See below.

<SPAN class=quote1>&gt; We didn't implement any form of role activation or deactivation, so
</SPAN><SPAN class=quote1>&gt; it could be argued that this is how we chose to handle sessions. This
</SPAN><SPAN class=quote1>&gt; could be one area to change.
</SPAN>
Yes. There's a standard statement

  SET ROLE

see below.

<SPAN class=quote1>&gt; We added six tables to the mysql database:
</SPAN><SPAN class=quote1>&gt; 	rbac_ua: User, Host, RoleName
</SPAN><SPAN class=quote1>&gt; 	rbac_pa: RoleName, Privileges
</SPAN><SPAN class=quote1>&gt; 	rbac_db: Db, RoleName, Privileges
</SPAN><SPAN class=quote1>&gt; 	rbac_tables_priv: Db, RoleName, Table_name, Privileges
</SPAN><SPAN class=quote1>&gt; 	rbac_columns_priv: Db, RoleName, Table_name, Column_name, Privileges
</SPAN><SPAN class=quote1>&gt; 	rbac_procs_priv: Db, RoleName, Routine_name, Routine_Type, Privileges
</SPAN>
But roles exist in the same namespace as usernames. Why not to put them
in existing tables user/db/tables_priv/columns_priv/procs_priv ?

<SPAN class=quote1>&gt; We tried to keep everything as close to the standard permission
</SPAN><SPAN class=quote1>&gt; tables as possible, but you can see we simplified some. For example,
</SPAN><SPAN class=quote1>&gt; the tables, columns, and procs privileges tables omit the Grantor and
</SPAN><SPAN class=quote1>&gt; Timestamp fields. We also only user user@host to authorize a user to
</SPAN><SPAN class=quote1>&gt; activate a role at login; for the final version, I think x509 and
</SPAN><SPAN class=quote1>&gt; other mechanisms would be desirable.
</SPAN>
You'll have it automatically if roles will be stored together with
users.

<SPAN class=quote1>&gt; We added several commands to manage roles such as
</SPAN><SPAN class=quote1>&gt; 	CREATE ROLE &lt;role_name&gt;
</SPAN>
ok.

<SPAN class=quote1>&gt; 	DROP ROLE &lt;role_name&gt;
</SPAN>
ok

<SPAN class=quote1>&gt; 	ADD TO ROLE &lt;role_name&gt; USER &lt;user_name&gt;@&lt;host_name_or_ip&gt;
</SPAN>
should be

   GRANT &lt;role_name&gt; TO &lt;user_name&gt;@&lt;host_name_or_ip&gt;

<SPAN class=quote1>&gt; 	DROP FROM ROLE &lt;role_name&gt; USER &lt;user_name&gt;@&lt;host_name_or_ip&gt;
</SPAN>
   REVOKE &lt;role_name&gt; FROM &lt;user_name&gt;@&lt;host_name_or_ip&gt;

<SPAN class=quote1>&gt; 	GRANT ... TO ROLE &lt;role_name&gt;
</SPAN>
Simply

   GRANT ... TO &lt;role_name&gt;

(same namespace, remember ?)

<SPAN class=quote1>&gt; Code wise, we placed virtually all of our added code in a mysql_rbac.
</SPAN><SPAN class=quote1>&gt; {h,cc} file and created an RBAC namespace. This made it easy for us
</SPAN><SPAN class=quote1>&gt; to see the places we had added code by searching the standard mysql
</SPAN><SPAN class=quote1>&gt; files for RBAC::.
</SPAN>
Good.

<SPAN class=quote1>&gt; That's a quick overview of what we were able to accomplish. I'm open
</SPAN><SPAN class=quote1>&gt; to suggestions on what should be added and what should be changed.
</SPAN><SPAN class=quote1>&gt; There are some things that should probably change. First is likely to
</SPAN><SPAN class=quote1>&gt; be the authentication and role activation would be nice if it
</SPAN><SPAN class=quote1>&gt; supported the other mechanisms that normal login supports. Also, we
</SPAN><SPAN class=quote1>&gt; probably don't want to modify the client.
</SPAN>
Here're few excerpts from our internal TODO list - the task "Roles"
(note - it assumes that roles will be in mysql.user table):

========================
CREATE ROLE
-----------

Syntax:
CREATE ROLE [ IF NOT EXISTS ] role_name

Required privilege: either you must have CREATE ROLE privilege, or you
must have INSERT privilege for the mysql.user table.

DROP ROLE
---------

Syntax:
DROP ROLE [ IF EXISTS ] role_name

Required privileges: either you must have CREATE ROLE privilege, or you
must have DELETE privilege for the mysql.user table.

GRANT privilege TO role_name
----------------------------

Syntax:
As in the MySQL Reference Manual,
<A href="http://dev.mysql.com/doc/refman/5.1/en/grant.html" rel=nofollow>http://dev.mysql.com/doc/refman/5.1/en/grant.html</A>

GRANT role_name TO user_name
----------------------------

To grant a role to a user:
GRANT role_name TO &lt;user name&gt;@&lt;host_name_or_ip&gt;

GRANT role_name TO role_name
----------------------------

To grant a role to a role:
GRANT role_name TO role_name
(optional feature)

CREATE ROLE privilege
---------------------

A privilege that allows one to create and drop roles.
GRANT CREATE ROLE ON *.* TO &lt;user name&gt;@&lt;host_name_or_ip&gt;

REVOKE
------

A REVOKE counterpart for every GRANT statement above:

REVOKE privilege FROM role_name
REVOKE role_name FROM &lt;user_name&gt;@&lt;host_name_or_ip&gt;
REVOKE role_name FROM role_name
REVOKE CREATE ROLE ON *.* FROM &lt;user name&gt;@&lt;host_name_or_ip&gt;

SET ROLE
--------

Syntax:
SET ROLE { role_name | NONE | DEFAULT }

Required privileges: a role must be granted first.

CURRENT_ROLE
------------

Syntax:
CURRENT_ROLE

This is a function which returns the current role, or NULL if there is
no current role.

This is analogous to the CURRENT_USER function.  The data type, width,
character set, and collation are the same as for CURRENT_USER.

SET DEFAULT ROLE
----------------

Syntax:
SET DEFAULT ROLE role_name TO user_name [,user_name...]
or
SET DEFAULT ROLE NONE TO user_name [,user_name...]

The idea of a "default role" is: if user joe has default role X,
then when  joe connects there is, in effect, an implicit
"SET ROLE X" execution.

Privileges required: CREATE USER or UPDATE on mysql.user table. We're
altering a user setting, and all user changes need a CREATE USER
privilege.

You do not need privileges on role_name to say "SET DEFAULT ROLE
role_name", you don't even need to know whether role_name exists.
However, when the grantee user connects again and the implicit "SET ROLE
X" occurs, the required privileges for SET ROLE are checked. If the
implicit SET ROLE fails, there is no warning at connect time.
========================

Hope this helps.

Regards / Mit vielen Grüssen,
Sergei

<SPAN class=signature>-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik &lt;serg@<I style="COLOR: #666">stripped</I>&gt;
 / /|_/ / // /\ \/ /_/ / /__  Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  MySQL GmbH, Radlkoferstr. 2, D-81373 München
       &lt;___/  Geschäftsführer: Hans von Bell, Kaj Arnö - HRB
München 162140
</SPAN></PRE>
<TABLE class=thread width="100%">
  <TBODY>
  <TR>
    <TH colSpan=3>Thread</TH></TR>
  <TR vAlign=top>
    <TD>• <A href="http://lists.mysql.com/internals/34330">RBAC Support</A></TD>
    <TD>Ian&nbsp;Molloy</TD>
    <TD>23&nbsp;Feb</A></TD></TR>
  <TR vAlign=top>
    <TD>&nbsp; • <A href="http://lists.mysql.com/internals/34331">Re: RBAC 
      Support</A></TD>
    <TD>Sergei&nbsp;Golubchik</TD>
    <TD>23&nbsp;Feb</A></TD></TR>
  <TR vAlign=top>
    <TD>&nbsp; &nbsp; • <A href="http://lists.mysql.com/internals/34355">Re: 
      RBAC Support</A></TD>
    <TD>Ian&nbsp;Molloy</TD>
    <TD>2&nbsp;Mar</A></TD></TR>
  <TR vAlign=top>
    <TD>&nbsp; &nbsp; &nbsp; • <B>Re: RBAC Support</B></TD>
    <TD>Sergei&nbsp;Golubchik</TD>
    <TD>7&nbsp;Mar</B></TD></TR></TBODY></TABLE>
<DIV id=footer>
<P>© 1995-2008 MySQL AB. All rights reserved.</P>
<UL style="FLOAT: right; LIST-STYLE-TYPE: none">
  <LI><A href="http://shop.mysql.com/">Online Shop</A> </LI>
  <LI><A href="http://www.mysql.com/sitemap.html">Site Map</A> </LI>
  <LI><A href="http://www.mysql.com/company/legal">Legal</A> </LI>
  <LI><A href="http://www.mysql.com/company/contact/">Contact Us</A> </LI>
  <LI class=last><A href="http://www.nosoftwarepatents.com/"><IMG height=15 
  alt="No Software Patents!" 
  src="Internal concerpts of MySQL about RBAC.files/nswpat80x15.gif" 
  width=80></A> </LI></UL><BR class=clear>
<P style="FLOAT: right">Page generated in 0.124 sec. using MySQL 5.1.14-beta-log 
</P></DIV></DIV></BODY></HTML>
